﻿
Imports System.Data
Imports System.Text  
Imports System.Data.SqlClient
Imports System.Collections.ObjectModel



Public Class CServicioLocalSQLServerDB


    Public Const PREFIJO_USUARIO As String = "U"
    PRIVATE Const  __DESCRIPCION_LISTED__ As Integer = 512
    

  Public Structure strucDatosDeSesion
        Public id As Integer 
        Public usuario As String 
  End Structure

  
  #Region "CATALOGOS CONFIGURACIÓN"

  
    Public Shared Function DarDeAltaFondo(ByRef regNvo As CFondo, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = " INSERT INTO MMA_CAT_FONDO_SEL (ID,TIPO) VALUES ( '" & regNvo.idNombre & "', " _
                                                                    & regNvo.Tipo & ");"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function DarDeAltaFolioDecalog(ByRef folioDec As CBitFolioDec, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer



        Dim id As Integer
        Dim fecha As Date

        id = CServicioLocalSQLServerDB.ObtenerIdSigTabla("MMA_OP_BIT_FOLIO_DEC")
        fecha = CServiciosMacrosSyBaseDB.FechaHoyDB()


        If id > 0 Then

            sSql = " INSERT INTO MMA_OP_BIT_FOLIO_DEC (ID, FOLIO_DEC,FUNCION,INTERMEDIARIO,FECHA_PROC,ESTATUS,NOM_ARCHIVO) VALUES (" _
                      & id & "," _
                      & IIf(folioDec.FolioDec.Equals(""), "NULL", folioDec.FolioDec) _
                      & "," & folioDec.FuncionId _
                      & "," & folioDec.Intermediario _
                      & ",'" & fecha.ToString("yyyy-MM-dd HH:mm:ss") & "'" _
                      & ", " & folioDec.Estatus & ",'" _
                      & folioDec.ArchivoAsociado.Trim() & "');"


            If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
                Return True
            Else
                Return False
            End If

        Else

            sMsgError = "No se pudo obtener el id para el folio"
            Return False
        End If


    End Function

    Public Shared Function ActualizarFolioEstatusDecalog(ByVal nomArchivo As String, ByVal nuevoEstatus As CBitFolioDec.estatusFolioDec, ByVal folioDecalog As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer



        sSql = " UPDATE MMA_OP_BIT_FOLIO_DEC SET ESTATUS =  " & nuevoEstatus _
                & ", FOLIO_DEC = '" & folioDecalog & "' WHERE " _
                & " NOM_ARCHIVO = '" & nomArchivo.Trim() & "';"


        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If



    End Function

    Public Shared Function ActualizarFondo(ByRef regActual As CFondo, regCambio As CFondo, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " UPDATE MMA_CAT_FONDO_SEL SET  ID = '" & regCambio.idNombre & "', TIPO = " _
                                                      & regCambio.Tipo & " WHERE ID = '" _
                                                      & regActual.idNombre & "' AND TIPO =" _
                                                      & regActual.Tipo & ";"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function EliminarFondo(ByRef regActual As CFondo, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer



        sSql = " DELETE FROM MMA_CAT_FONDO_SEL WHERE  ID = '" & regActual.idNombre & "' and TIPO = " _
                                                             & regActual.Tipo & ";"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ObtenerFondo(ByVal idNombre As String, ByVal obtenerTodos As Boolean) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            If obtenerTodos = True Then
                sSql = " SELECT ID, TIPO FROM MMA_CAT_FONDO_SEL;"
            Else
                sSql = " SELECT ID, TIPO FROM MMA_CAT_FONDO_SEL WHERE  ID = '" & idNombre & "';"
            End If


            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtenerFondo(ByVal tipo As CDefinicionesGrales.TiposFondo) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try


            sSql = " SELECT ID, TIPO FROM MMA_CAT_FONDO_SEL WHERE  TIPO = " & tipo & ";"


            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtenerFechasNoLab(ByVal idFuncionalidad As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT distinct(fecha)  from MMA_CAT_FECHA_NO_LAB WHERE ID_FUNCION = " & idFuncionalidad

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtenerRegFoliosDecXEstatus(ByVal estatus As CBitFolioDec.estatusFolioDec) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFoliosDec As New DataTable()

        Try


            sSql = "SELECT A.ID, A.FOLIO_DEC,A.FUNCION as FUNCION_ID, B.FUNCION_DESC AS FUNCION,A.INTERMEDIARIO, A.FECHA_PROC," _
                   & " CASE ESTATUS WHEN 0 THEN 'PENDIENTE' " _
                   & " WHEN 1 THEN 'PROCESADO' " _
                   & " WHEN 2 THEN 'ERRÓNEO' " _
                   & " END as ESTATUS_DES, " _
                   & " ESTATUS, " _
                   & " A.NOM_ARCHIVO " _
                   & " FROM MMA_OP_BIT_FOLIO_DEC A INNER JOIN MMA_CAT_FUNCION B ON (A.FUNCION = B.ID) WHERE A.ESTATUS = " & estatus & " ORDER BY A.FECHA_PROC"


            dtFoliosDec = db.EjecutarConsulta(sSql, pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFoliosDec

        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Shared Function ObtenerRegFoliosDec(ByVal fechaIni As String, ByVal fechaFin As String, ByVal hayCondicion As Boolean) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFoliosDec As New DataTable()

        Try

            If hayCondicion Then
                sSql = "SELECT A.ID, A.FOLIO_DEC,A.FUNCION as FUNCION_ID, B.FUNCION_DESC AS FUNCION,A.INTERMEDIARIO, A.FECHA_PROC," _
                       & " CASE ESTATUS WHEN 0 THEN 'PENDIENTE' " _
                       & " WHEN 1 THEN 'PROCESADO' " _
                       & " WHEN 2 THEN 'ERRÓNEO' " _
                       & " END as ESTATUS_DES, " _
                       & " ESTATUS, " _
                       & " A.NOM_ARCHIVO " _
                       & " FROM MMA_OP_BIT_FOLIO_DEC A INNER JOIN MMA_CAT_FUNCION B ON (A.FUNCION = B.ID) WHERE A.FECHA_PROC BETWEEN " _
                     & "'" & Convert.ToDateTime(fechaIni).ToString("yyyy-MM-dd HH:mm:ss") & "' AND '" & Convert.ToDateTime(fechaFin).ToString("yyyy-MM-dd HH:mm:ss") & "' ORDER BY A.FECHA_PROC"


            Else
                sSql = "SELECT A.ID, A.FOLIO_DEC,A.FUNCION as FUNCION_ID,B.FUNCION_DESC AS FUNCION, A.INTERMEDIARIO, A.FECHA_PROC, " _
                         & " CASE ESTATUS WHEN 0 THEN 'PENDIENTE' " _
                         & " WHEN 1 THEN 'PROCESADO' " _
                         & " WHEN 2 THEN 'ERRÓNEO' " _
                         & " END as ESTATUS_DES, " _
                         & " ESTATUS, " _
                         & " A.NOM_ARCHIVO " _
                         & "from MMA_OP_BIT_FOLIO_DEC A INNER JOIN MMA_CAT_FUNCION B ON (A.FUNCION = B.ID) ORDER BY FECHA_PROC "


            End If

            dtFoliosDec = db.EjecutarConsulta(sSql, pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFoliosDec

        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Shared Function ObtenerRegistrosBit(ByVal fechaIni As String, ByVal fechaFin As String, ByVal hayCondicion As Boolean) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            If hayCondicion Then
                sSql = "SELECT FECHA_INI, FECHA_FIN,USUARIO, MODULO,FUNCION,DESCRIPCION  from MMA_OP_BITACORA WHERE FECHA_INI BETWEEN " _
                     & " '" & Convert.ToDateTime(fechaIni).ToString("yyyy-MM-dd HH:mm:ss") & "' AND '" & Convert.ToDateTime(fechaFin).ToString("yyyy-MM-dd HH:mm:ss") & "' ORDER BY FECHA_INI"
            Else
                sSql = "SELECT  FECHA_INI, FECHA_FIN,USUARIO, MODULO,FUNCION,DESCRIPCION  from MMA_OP_BITACORA ORDER BY FECHA_INI"
            End If


            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo

        Catch ex As Exception
            Throw ex
        End Try


    End Function


    Public Shared Function DarDeAltaFechaNoLabLiqPorv(ByVal fecha As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " INSERT INTO MMA_CAT_FECHA_NO_LAB (ID_FUNCION,FECHA) VALUES (" & 1 & ", '" & fecha & "');"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ActualizarFechaNoLabLiqPorv(ByVal fechaAct As String, ByVal fechaNva As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " UPDATE MMA_CAT_FECHA_NO_LAB SET FECHA = '" & fechaNva & "' WHERE ID_FUNCION = 1 AND FECHA = '" & fechaAct & "'"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function EliminarFechaNoLabLiqPorv(ByVal fechaAct As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " DELETE FROM MMA_CAT_FECHA_NO_LAB WHERE FECHA = '" & fechaAct & "' AND  ID_FUNCION = 1 "

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function EliminarFechaNoLabLiqPorvMasiva(ByVal parametrofechas As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " DELETE FROM MMA_CAT_FECHA_NO_LAB WHERE FECHA in (" & parametrofechas & ") AND  ID_FUNCION = 1 "

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function DarDeAltaFechaNoLabValmer(ByVal fecha As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " INSERT INTO MMA_CAT_FECHA_NO_LAB (ID_FUNCION,FECHA) VALUES (" & 12 & ", '" & fecha & "');"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ActualizarFechaNoLabValmer(ByVal fechaAct As String, ByVal fechaNva As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " UPDATE MMA_CAT_FECHA_NO_LAB SET FECHA = '" & fechaNva & "' WHERE ID_FUNCION = 12 AND FECHA = '" & fechaAct & "'"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function EliminarFechaNoLabValmer(ByVal fechaAct As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " DELETE FROM MMA_CAT_FECHA_NO_LAB WHERE FECHA = '" & fechaAct & "' AND  ID_FUNCION = 12 "

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function EliminarFechaNoValmerMasiva(ByVal parametrofechas As String, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " DELETE FROM MMA_CAT_FECHA_NO_LAB WHERE FECHA in (" & parametrofechas & ") AND  ID_FUNCION = 12 "

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ObtieneParametrosCompletosTraspasos() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT ID,PARAMETRO_DESC,PARAMETRO_VALOR FROM MMA_CAT_PARAMETRO_TRASPASO ORDER BY ID"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ActualizaParametroTraspaso(ByVal intClave As Integer, ByVal strValor As String, ByVal intUsuario As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = "UPDATE MMA_CAT_PARAMETRO_TRASPASO SET PARAMETRO_VALOR = '" & strValor & "', USUARIO_MODIFICACION = " & intUsuario & ", FECHA_MODIFICACION = GETDATE() WHERE ID = " & intClave

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function ActualizaMensajeErrorDECALOG(ByVal intId As Integer, ByVal strMensaje As String, ByVal intUsuario As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try
            sSql = "UPDATE MMA_OP_BITACORA_DECALOG SET FECHA_MODIFICACION = GETDATE(), USUARIO_MODIFICACION = " & intUsuario & ",MENSAJE_ERROR = '" & strMensaje & "' WHERE ID = " & intId

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ActualizaClavesOp(ByRef lista As ObservableCollection(Of CClavesOp), ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer
        Try
            sSql = "delete MMA_CAT_CLAVES_OP; "
            Dim count As Integer = 1
            For Each clave As CClavesOp In lista
                sSql = sSql & "insert into MMA_CAT_CLAVES_OP(ID_CLAVE_OP, MERCADO, TIPO_INVERSION, TIPO_TRANSACCION, TIPO_OPERACION, INVERSION_COVAFSYS, TRANSACCION_COVAFSYS, DESCRIPCION_COVAFSYS, COMISION, IMPUESTO, CALCULAR)" _
                 & " values (" & count & ",'" & clave.Mercado _
                 & "','" & clave.TipoInversion _
                 & "','" & clave.TipoTransaccion _
                 & "','" & clave.TipoOperacion _
                 & "','" & clave.InversionCovafsys _
                 & "','" & clave.TransaccionCovafsys _
                 & "','" & clave.DescripcionCovafsys _
                 & "'," & clave.Comision _
                 & "," & clave.Impuesto _
                 & "," & clave.CalcularDB _
                 & "); "
                count = count + 1
            Next
        Catch ex As Exception
            sMsgError = ex.Message
            Return False
        End Try

        
        

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If
    End Function

 #End Region

  #Region "GENERALES"

 
    Public Shared Function GrabarBitacoraBD(ByRef registro As CRegistroBitacora) As Integer

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer
        Dim sMsgError As String = ""

        Dim id As Integer


        id = CServicioLocalSQLServerDB.ObtenerIdSigTabla("MMA_OP_BITACORA")

        If id <= -1 Then
            Return -1
        End If

        sSql = " INSERT INTO MMA_OP_BITACORA (ID, FECHA_INI, FECHA_FIN, USUARIO,MODULO,FUNCION,DESCRIPCION) VALUES ('" _
                 & id & "',  '" _
                 & registro.FechaIni.ToString("yyyy-MM-dd HH:mm:ss") & "', " _
                 & " NULL " & ", '" _
                 & registro.Usuario & "', '" _
                 & registro.Modulo & "', '" _
                 & registro.Funcion & "', '" _
                 & registro.Descripcion.Replace("'", "''") & "');"


        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return id
        Else
            Return -1
        End If


    End Function

    Public Shared Sub CompletarFechaFin(ByVal id As Integer, ByVal fechaFin As String)

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer
        Dim sMsgError As String = ""



        sSql = " UPDATE MMA_OP_BITACORA SET FECHA_FIN = '" & Convert.ToDateTime(fechaFin).ToString("yyyy-MM-dd HH:mm:ss") & "' WHERE ID = " & id
        db.EjecutarComando(sSql, pm, regAfec, sMsgError)

    End Sub

    Public Shared Function ObtenerIdSigTabla(ByVal tabla As String) As Integer

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()


        sSql = " SELECT MAX(ID) as id FROM  " & tabla

        dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return -1
        End If

        If dtFondo.Rows.Count = 1 Then
            If IsDBNull(dtFondo.Rows(0).Item("ID")) = False Then
                Return dtFondo.Rows(0).Item("ID") + 1
            Else
                Return 1
            End If

        Else
            Return 1
        End If


    End Function

#End Region

  #Region "SESION"


    Public Shared Function ObtenerDatosSesion(ByVal usuario As String) As CServicioLocalSQLServerDB.strucDatosDeSesion  

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtSesion As New DataTable()

        Try

          
            sSql = " SELECT ID, USUARIO FROM MMA_OP_SESION WHERE  USUARIO = '" & usuario  & "';"
           
            dtSesion = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If


            If dtSesion.Rows.Count = 1 Then
                If IsDBNull(dtSesion.Rows(0).Item("ID")) = False Then
                    ObtenerDatosSesion.id = dtSesion.Rows(0).Item("ID")
                    ObtenerDatosSesion.usuario = dtSesion.Rows(0).Item("USUARIO")
                    Return ObtenerDatosSesion
                Else
                    ObtenerDatosSesion.id = 0
                    ObtenerDatosSesion.usuario = "XYZ"
                End If
           Elseif dtSesion.Rows.Count = 0
                     ObtenerDatosSesion.id = 0
                     ObtenerDatosSesion.usuario = "XYZ"
           Else

                Throw New Exception ("Error de consistencia: varias sesiones abiertas")
           End If


          Return ObtenerDatosSesion
                    
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtenerDatosSesion(ByVal id As Integer,   ByVal usuario As String) As CServicioLocalSQLServerDB.strucDatosDeSesion  

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtSesion As New DataTable()

        Try

          
            sSql = " SELECT ID, USUARIO FROM MMA_OP_SESION WHERE  ID = " & ID  & " AND   USUARIO = '" & usuario  & "' ;"
           
            dtSesion = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If


            If dtSesion.Rows.Count = 1 Then
                If IsDBNull(dtSesion.Rows(0).Item("ID")) = False Then
                    ObtenerDatosSesion.id = dtSesion.Rows(0).Item("ID")
                    ObtenerDatosSesion.usuario = dtSesion.Rows(0).Item("USUARIO")
                    Return ObtenerDatosSesion
                Else
                    ObtenerDatosSesion.id = 0
                    ObtenerDatosSesion.usuario = "XYZ"
                End If
           Elseif dtSesion.Rows.Count = 0
                     ObtenerDatosSesion.id = 0
                     ObtenerDatosSesion.usuario = "XYZ"
           Else

                Throw New Exception ("Error de consistencia: varias sesiones abiertas")
           End If


          Return ObtenerDatosSesion
                    
        Catch ex As Exception
            Throw ex
        End Try

    End Function
    
    Public Shared Function AltaDeSesion(ByVal usuario As String, byref sMsgError As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String        
        Dim iNumReg As Integer 
                
        Dim idSesion As Integer 


        Try

            idSesion = ObtenerIdSigTabla ("MMA_OP_SESION")


            sSql = "INSERT INTO MMA_OP_SESION (ID,USUARIO) VALUES (" & idSesion  & ",'" & usuario  & "');"

            return db.EjecutarComando (sSql,pm,iNumReg,sMsgError) 

            
        Catch ex As Exception
            sMsgError = sMsgError & ex.Message 
            Return False 
        End Try
    End Function

    Public Shared Function EliminarSesion(ByVal idSesion As Integer,  ByVal usuario As String, byref sMsgError As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String        
        Dim iNumReg As Integer 
                       
        Try            
            sSql = "DELETE FROM MMA_OP_SESION  WHERE ID = " & idSesion & " AND USUARIO='" & usuario & "';"
            return db.EjecutarComando (sSql,pm,iNumReg,sMsgError) 
            
        Catch ex As Exception
             sMsgError = sMsgError & ex.Message 
             Return False 
        End Try
    End Function

    Public Shared Function ObtenerFuncionesXUsuario(ByVal usuarioDec As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtAreaFuncion As New DataTable()

        Try

            sSql = " SELECT "
            sSql = sSql & " D.ID AS ID_AREA, "
            sSql = sSql & " D.AREA_DESC AS AREA, "
            sSql = sSql & " C.ID AS ID_FUNCION, "
            sSql = sSql & " C.FUNCION_DESC AS FUNCION   "
            sSql = sSql & " FROM "
            sSql = sSql & " MMA_CAT_USUARIO A "
            sSql = sSql & " INNER JOIN MMA_REL_USU_FUN B ON (A.ID = B.ID_USUARIO) "
            sSql = sSql & " INNER JOIN MMA_CAT_FUNCION C ON (B.ID_FUNCION = C.ID)  "
            sSql = sSql & " INNER JOIN MMA_CAT_AREA D    ON (C.ID_AREA = D.ID)  "
            sSql = sSql & " WHERE ID_DECALOG = '" & usuarioDec & "' ORDER BY D.ID"


            dtAreaFuncion = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtAreaFuncion

        Catch ex As Exception
            Throw ex
        End Try

    End Function

#End Region

  #Region "PERFILES DE USUARIO"


    Public Shared Function ValidarAltaUsuario(ByVal usuario As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtDatos As New DataTable()

        Try

            sSql = "SELECT * FROM MMA_CAT_USUARIO WHERE ID_DECALOG = '" & usuario & "' AND ACTIVO = 1"

            dtDatos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            If dtDatos.Rows.Count = 1 Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        End Try

    End Function

    Public Shared Function ObtieneDatosUsuario(ByVal strClaveDECALOG As String) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT TOP 1 ID,NOMBRE FROM MMA_CAT_USUARIO WHERE ID_DECALOG = '" & strClaveDECALOG & "'"

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return Nothing
            End If

            If dtFondo.Rows.Count = 1 Then
                Return dtFondo
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Shared Function ObtieneUsuarios(ByVal IdUsuario As Integer) As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT ID,ID_DECALOG CLAVE,NOMBRE,CASE WHEN ACTIVO = 1 THEN 'Activo' ELSE 'Inactivo' END ESTATUS,ACTIVO FROM MMA_CAT_USUARIO WHERE ID <> " & IdUsuario

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneAreas() As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT ID, AREA_DESC FROM MMA_CAT_AREA ORDER BY AREA_DESC"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneFunciones() As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT ID,FUNCION_DESC,ID_AREA FROM MMA_CAT_FUNCION "

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneFuncionesUsuario(ByVal IdUsuario As Integer) As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT DISTINCT ID_FUNCION FROM MMA_REL_USU_FUN WHERE ID_USUARIO = " & IdUsuario

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function InsertaUsuario(ByVal usuario As CUsuario, ByVal dtfunciones As DataTable, ByVal Activo As Boolean) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = "", strCadenaFunciones As String = String.Empty
        Dim intActivo As Int16

        If Activo Then
            intActivo = 1
        Else
            intActivo = 0
        End If

        For Each drAux As DataRow In dtfunciones.Rows
            strCadenaFunciones = strCadenaFunciones & drAux(0) & ","
        Next

        strCadenaFunciones = strCadenaFunciones.Substring(0, strCadenaFunciones.Length - 1)

        Dim dtDatos As New DataTable()

        Try

            sSql = " DECLARE @Max INT SELECT @Max = MAX(ID) FROM MMA_CAT_USUARIO SET NOCOUNT ON BEGIN TRY INSERT INTO MMA_CAT_USUARIO(ID,ID_DECALOG,NOMBRE,ACTIVO) VALUES (@Max+1,'" & usuario.Clave.Trim & "','" & usuario.Nombre & "'," & intActivo & ")"
            sSql = sSql & " INSERT INTO MMA_REL_USU_FUN (ID_USUARIO,ID_FUNCION ) SELECT @Max+1,ID FROM MMA_CAT_FUNCION WHERE ID IN (" & strCadenaFunciones & ")"
            sSql = sSql & " SELECT 0 Error END TRY BEGIN CATCH SELECT 1 Error END CATCH"

            dtDatos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtDatos.Rows.Count = 1 Then
                Return Convert.ToInt32(dtDatos(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function ActualizaUsuario(ByVal usuario As CUsuario, ByVal dtFunciones As DataTable, ByVal Activo As Boolean) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = "", strCadenaFunciones As String = String.Empty
        Dim intActivo As Integer

        If Activo Then
            intActivo = 1
        Else
            intActivo = 0
        End If

        For Each drAux As DataRow In dtFunciones.Rows
            strCadenaFunciones = strCadenaFunciones & drAux(0) & ","
        Next

        strCadenaFunciones = strCadenaFunciones.Substring(0, strCadenaFunciones.Length - 1)

        Dim dtDatos As New DataTable()

        Try

            sSql = "SET NOCOUNT ON BEGIN TRY UPDATE MMA_CAT_USUARIO SET ACTIVO = " & intActivo & " WHERE ID = " & usuario.Id & " DELETE MMA_REL_USU_FUN WHERE ID_USUARIO = " & usuario.Id
            sSql = sSql & " INSERT INTO MMA_REL_USU_FUN (ID_USUARIO,ID_FUNCION ) SELECT " & usuario.Id & ",ID FROM MMA_CAT_FUNCION WHERE ID IN (" & strCadenaFunciones & ") SELECT 0 Error END TRY"
            sSql = sSql & " BEGIN CATCH SELECT 1 Error END CATCH"

            dtDatos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtDatos.Rows.Count = 1 Then
                Return Convert.ToInt32(dtDatos(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function EliminaUsuario(ByVal usuario As CUsuario) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtDatos As New DataTable()

        Try

            sSql = "UPDATE MMA_CAT_USUARIO SET ACTIVO = 0 WHERE ID = " & usuario.Id
            

            dtDatos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function


   Public Shared Function ObtenerUsuDecalogConUsuRed(ByVal pUsuRed As String, ByRef sMsgError As String) As String 
   

        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        

        Dim strConsulta As String = "SELECT ID_DECALOG FROM MMA_CAT_USUARIO WHERE USU_RED = '" & pUsuRed.ToUpper.Trim  () & "'"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return ""
        End If


        If dtDatos.Rows.Count = 1 then 
           Return dtDatos.Rows(0)("ID_DECALOG")
        else
           sMsgError = "Información del usuario inconsistente"
           Return ""
        End If        

   End Function



#End Region


  #Region "COVAF-REPORTING-UDI..."


    Public Shared Function ObtenerTipos(ByVal intFuncion As Integer) As List(Of CTipo)
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTipos As New DataTable()
        Dim lstTipo As New List(Of CTipo)

        Try
            'yyy
            'IAAL Se agrega solucion temporal por desfase de ID's
            'sSql = "SELECT (ID_TIPO - 23) ID_TIPO,TIPO_DESC FROM MMA_CAT_TIPO WHERE ID_FUNCION = " + intFuncion.ToString + " ORDER BY TIPO_DESC "
            sSql = "SELECT ID_TIPO,TIPO_DESC FROM MMA_CAT_TIPO WHERE ID_FUNCION = " + intFuncion.ToString + " ORDER BY TIPO_DESC "
            'IAAL Se agrega solucion temporal por desfase de ID's

            dtTipos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            If dtTipos.Rows.Count > 0 Then
                For Each reg As DataRow In dtTipos.Rows
                    Dim tipo As New CTipo
                    With tipo
                        .Id = reg("ID_TIPO")
                        .Descripcion = reg("TIPO_DESC")
                    End With
                    lstTipo.Add(tipo)
                    tipo = Nothing
                Next
            End If

            Return lstTipo

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneRutaUDI() As String
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTipos As New DataTable()


        Try

            sSql = "SELECT REPLACE(REPLACE(PARAMETRO_VALOR,'Anio1',CONVERT(VARCHAR,DATEPART(YEAR,GETDATE())-1)),'Anio2',CONVERT(VARCHAR,DATEPART(YEAR,GETDATE()))) FROM MMA_PARAMETROS WHERE Id = 1"

            dtTipos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTipos.Rows(0)(0)

        Catch ex As Exception
            Throw ex
        End Try
    End Function
   
    Public Shared Function ObtieneCampos(ByVal Tipo As CTipo) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtCampos As New DataTable()

        Try
            'yyy
            'IAAL Se agrega solucion temporal por desfase de ID's
            'sSql = "SELECT CAMPO_DESC,CAMPO_FORMULA FROM MMA_CAT_CAMPO_TIPO WHERE (ID_TIPO - 23)= " + Tipo.Id.ToString + " ORDER BY ID_CAMPO"
            sSql = "SELECT CAMPO_DESC,CAMPO_FORMULA FROM MMA_CAT_CAMPO_TIPO WHERE ID_TIPO = " + Tipo.Id.ToString + " ORDER BY ID_CAMPO"
            'IAAL Se agrega solucion temporal por desfase de ID's

            dtCampos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtCampos

        Catch ex As Exception
            Throw ex
        End Try
    End Function
  
    Public Shared Function ObtieneFechasBorrado() As List(Of CFechaBorrado)
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFechas As New DataTable()
        Dim lstFechas As New List(Of CFechaBorrado)

        Try

            sSql = "SELECT ID_FECHA,FECHA_DESC FROM MMA_CAT_FECHA ORDER BY ID_FECHA"

            dtFechas = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            If dtFechas.Rows.Count > 0 Then
                For Each reg As DataRow In dtFechas.Rows
                    Dim fecha As New CFechaBorrado
                    With fecha
                        .Id = reg("ID_FECHA")
                        .Descripcion = reg("FECHA_DESC")
                    End With
                    lstFechas.Add(fecha)
                    fecha = Nothing
                Next
            End If

            Return lstFechas

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneSegundosEspera() As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTipos As New DataTable()

        Try

            sSql = "SELECT CONVERT(INT,PARAMETRO_VALOR) FROM MMA_PARAMETROS WHERE Id = 7"

            dtTipos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return Convert.ToInt32(dtTipos.Rows(0)(0))

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneParametrosTraspasos() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT PARAMETRO_VALOR FROM MMA_CAT_PARAMETRO_TRASPASO ORDER BY ID"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try

    End Function
  
    Public Shared Function ObtieneParametrosFTP() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT PARAMETRO_VALOR FROM MMA_PARAMETROS WHERE ID BETWEEN 13 AND 17 ORDER BY ID"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function 

    Public Shared Function ObtieneTiposInstrumentos() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT ID_TIPO_INSTRUMENTO ID,TIPO_INSTRUMENTO_NOMBRE NOMBRE, TIPO_INSTRUMENTO_CLASE CLASE,TIPO_INSTRUMENTO_GRUPO GRUPO FROM MMA_CAT_TIPO_INSTRUMENTO ORDER BY ID"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneCabeceraReporting() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SET NOCOUNT ON UPDATE MMA_CAT_CABECERA_REPORTING SET VALOR_CABECERA = CONVERT(VARCHAR,GETDATE(),5) WHERE CABECERA = 'ORDR_VALID_DAT'"
            sSql = sSql + " SELECT CABECERA,VALOR_CABECERA,DETALLE,VALOR_DETALLE,CABECERA_GEN,VALOR,CABECERA_GEN_2,VALOR_2 FROM MMA_CAT_CABECERA_REPORTING"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneBrokersPorvenir() As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT FAX_NUM,BROKER_COD FROM MMA_CAT_BROKER_PORVENIR"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneClavesOp() As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT * FROM MMA_CAT_CLAVES_OP ORDER BY ID_CLAVE_OP"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function AltaBrokerPorvenir(ByVal strFax As String, ByVal strBroker As String, ByVal intUsuario As Integer) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_BROKER_PORVENIR WHERE FAX_NUM = '" & strFax & "')"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_BROKER_PORVENIR (FAX_NUM,BROKER_COD,USUARIO_ALTA) VALUES ('" & strFax & "','" & strBroker & "'," & intUsuario & ")"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtBroker.Rows.Count = 1 Then
                Return Convert.ToInt32(dtBroker(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function ActualizaBrokerPorvenir(ByVal strFaxAnterior As String, ByVal strBrokerAnterior As String, ByVal strFaxNuevo As String, ByVal strBrokerNuevo As String, ByVal intUsuario As Integer, ByVal intOpcion As Int16)
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            If intOpcion = 0 Then
                sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_BROKER_PORVENIR WHERE FAX_NUM = '" & strFaxNuevo & "')"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_BROKER_PORVENIR SET FAX_NUM = '" & strFaxNuevo & "',BROKER_COD = '" & strBrokerNuevo & "',USUARIO_MODIFICACION= " & intUsuario & ",FECHA_MODIFICACION=GETDATE() WHERE FAX_NUM ='" & strFaxAnterior & "' AND BROKER_COD = '" & strBrokerAnterior & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
            Else
                sSql = "UPDATE MMA_CAT_BROKER_PORVENIR SET BROKER_COD = '" & strBrokerNuevo & "',USUARIO_MODIFICACION= " & intUsuario & ",FECHA_MODIFICACION=GETDATE() WHERE FAX_NUM ='" & strFaxAnterior & "' SELECT 0 Error"
            End If

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtBroker.Rows.Count = 1 Then
                Return Convert.ToInt32(dtBroker(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function EliminaBrokerPorvenir(ByVal strFax As String, ByVal strBroker As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE MMA_CAT_BROKER_PORVENIR WHERE FAX_NUM = '" & strFax & "' AND BROKER_COD = '" & strBroker & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function InsertaBitacoraDECALOG(ByVal intFuncion As Integer, ByVal strNombreArchivo As String, ByVal intUsuario As Integer) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try
            sSql = "INSERT INTO MMA_OP_BITACORA_DECALOG (ID_FUNCION,NOMBRE_ARCHIVO,USUARIO_ALTA) VALUES (" & intFuncion & ",'" & strNombreArchivo & "'," & intUsuario & ") SELECT SCOPE_IDENTITY() CLAVE"

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return 0
            End If

            If dtFondo.Rows.Count = 1 Then
                Return dtFondo(0)("CLAVE")
            Else
                Return 0
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ActualizaBitacoraDECALOG(ByVal intId As Integer, ByVal intEstatus As Integer, ByVal intUsuario As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try
            sSql = "UPDATE MMA_OP_BITACORA_DECALOG SET FECHA_MODIFICACION = GETDATE(), USUARIO_MODIFICACION = " & intUsuario & ",ESTATUS = " & intEstatus & " WHERE ID = " & intId

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ActualizaFolioDECALOG(ByVal intId As Integer, ByVal strFolio As String, ByVal intUsuario As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try
            sSql = "UPDATE MMA_OP_BITACORA_DECALOG SET FECHA_MODIFICACION = GETDATE(), USUARIO_MODIFICACION = " & intUsuario & ",FOLIO_DECALOG = '" & strFolio & "' WHERE ID = " & intId

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneBitacoraDECALOG(ByVal intFuncion As Integer, ByVal strFechaInicio As String, ByVal strFechaFin As String) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SET LANGUAGE SPANISH"
            sSql = sSql & " SELECT A.ID,A.ESTATUS IDESTATUS,CASE A.ESTATUS WHEN 0 THEN 'Pendiente' WHEN 1 THEN 'Procesado' WHEN 2 THEN 'Erróneo' WHEN 3 THEN 'Parcialmente procesado' END ESTATUS,A.NOMBRE_ARCHIVO ARCHIVO,A.FOLIO_DECALOG FOLIO,"
            sSql = sSql & " B.NOMBRE USUARIO,A.FECHA_ALTA FECHA, A.MENSAJE_ERROR MENSAJEERROR, A.DETALLE_ERROR DETALLEERROR"
            sSql = sSql & " FROM MMA_OP_BITACORA_DECALOG A INNER JOIN MMA_CAT_USUARIO B"
            sSql = sSql & " ON A.USUARIO_ALTA = B.ID"
            sSql = sSql & " WHERE ID_FUNCION = " & intFuncion
            sSql = sSql & " AND A.FECHA_ALTA BETWEEN '" & strFechaInicio & "' AND '" & strFechaFin & "'"
            sSql = sSql & " ORDER BY A.ID"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtieneFondoBench() As DataTable
        Dim dbCnn As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT FONDO,BENCH FROM MMA_CAT_FONDO_BENCH"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function AltaFondoBench(ByVal strFondo As String, ByVal strBench As String, ByVal intUsuario As Integer) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_FONDO_BENCH WHERE FONDO = '" & strFondo & "')"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_FONDO_BENCH (FONDO,BENCH,USUARIO_ALTA) VALUES ('" & strFondo & "','" & strBench & "'," & intUsuario & ")"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtBroker.Rows.Count = 1 Then
                Return Convert.ToInt32(dtBroker(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function ActualizaFondoBench(ByVal strFondoAnterior As String, ByVal strBenchAnterior As String, ByVal strFondoNuevo As String, ByVal strBenchNuevo As String, ByVal intUsuario As Integer, ByVal intOpcion As Int16)
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            If intOpcion = 0 Then
                sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_FONDO_BENCH WHERE FONDO = '" & strFondoNuevo & "')"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_FONDO_BENCH SET FONDO = '" & strFondoNuevo & "',BENCH = '" & strBenchNuevo & "',USUARIO_MODIFICACION= " & intUsuario & ",FECHA_MODIFICACION=GETDATE() WHERE FONDO ='" & strFondoAnterior & "' AND BENCH = '" & strBenchAnterior & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
            Else
                sSql = "UPDATE MMA_CAT_FONDO_BENCH SET BENCH = '" & strBenchNuevo & "',USUARIO_MODIFICACION= " & intUsuario & ",FECHA_MODIFICACION=GETDATE() WHERE FONDO ='" & strFondoAnterior & "' SELECT 0 Error"
            End If

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtBroker.Rows.Count = 1 Then
                Return Convert.ToInt32(dtBroker(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function EliminaFondoBench(ByVal strFondo As String, ByVal strBench As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE MMA_CAT_FONDO_BENCH WHERE FONDO = '" & strFondo & "' AND BENCH = '" & strBench & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function AltaTipoInstrumento(ByVal strTipoInstrumento As String, ByVal strNombre As String, ByVal strClase As String, ByVal strGrupo As String, ByVal intUsuario As Integer) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_TIPO_INSTRUMENTO WHERE ID_TIPO_INSTRUMENTO = " & strTipoInstrumento & ")"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_TIPO_INSTRUMENTO (ID_TIPO_INSTRUMENTO,TIPO_INSTRUMENTO_NOMBRE,TIPO_INSTRUMENTO_CLASE,TIPO_INSTRUMENTO_GRUPO,USUARIO_ALTA) VALUES (" & strTipoInstrumento & ",'" & strNombre & "','" & strClase & "','" & strGrupo & "'," & intUsuario & ")"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtBroker.Rows.Count = 1 Then
                Return Convert.ToInt32(dtBroker(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function ActualizaTipoInstrumento(ByVal strTipoInstrumentoAnterior As String, ByVal strNombreAnterior As String, ByVal strClaseAnterior As String, ByVal strGrupoAnterior As String, ByVal strTipoInstrumentoNuevo As String, ByVal strNombreNuevo As String, ByVal strClaseNuevo As String, ByVal strGrupoNuevo As String, ByVal intUsuario As Integer, ByVal intOpcion As Int16)
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            If intOpcion = 0 Then
                sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_TIPO_INSTRUMENTO WHERE ID_TIPO_INSTRUMENTO = " & strTipoInstrumentoNuevo & ")"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_TIPO_INSTRUMENTO SET ID_TIPO_INSTRUMENTO = '" & strTipoInstrumentoNuevo & "',TIPO_INSTRUMENTO_NOMBRE = '" & strNombreNuevo & "',TIPO_INSTRUMENTO_CLASE = '" & strClaseNuevo & "',TIPO_INSTRUMENTO_GRUPO = '" & strGrupoNuevo & "',USUARIO_MODIFICACION= " & intUsuario & ",FECHA_MODIFICACION=GETDATE() WHERE ID_TIPO_INSTRUMENTO = " & strTipoInstrumentoAnterior & " AND TIPO_INSTRUMENTO_NOMBRE = '" & strNombreAnterior & "' AND TIPO_INSTRUMENTO_CLASE = '" & strClaseAnterior & "' AND TIPO_INSTRUMENTO_GRUPO = '" & strGrupoAnterior & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
            Else
                sSql = "UPDATE MMA_CAT_TIPO_INSTRUMENTO SET TIPO_INSTRUMENTO_NOMBRE = '" & strNombreNuevo & "',TIPO_INSTRUMENTO_CLASE = '" & strClaseNuevo & "',TIPO_INSTRUMENTO_GRUPO = '" & strGrupoNuevo & "',USUARIO_MODIFICACION= " & intUsuario & ",FECHA_MODIFICACION=GETDATE() WHERE ID_TIPO_INSTRUMENTO = " & strTipoInstrumentoAnterior & " SELECT 0 Error"
            End If

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            If dtBroker.Rows.Count = 1 Then
                Return Convert.ToInt32(dtBroker(0)("Error"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function EliminaTipoInstrumento(ByVal strTipoInstrumento As String, ByVal strNombre As String, ByVal strClase As String, ByVal strGrupo As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE MMA_CAT_TIPO_INSTRUMENTO WHERE ID_TIPO_INSTRUMENTO = " & strTipoInstrumento & " AND TIPO_INSTRUMENTO_NOMBRE = '" & strNombre & "' AND TIPO_INSTRUMENTO_CLASE = '" & strClase & "' AND TIPO_INSTRUMENTO_GRUPO = '" & strGrupo & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function



    'INI Candado ejecucion UDI 2015-04-10 IRI

     'PARAMETRO ID 80 (Almacena la fecha de la última ejecución)

     Public Shared Function ActualizaFechaEjecUDI(ByVal pFechaHoraEjec As String, ByRef sMsgError As String ) As Boolean 
         Dim db        As New CBaseDeDatosSQLServer()
         Dim pm        As New List(Of SqlParameter)         
         Dim regAfec   As Integer = 0     
         
                          
         Dim sql        = New  StringBuilder ("")

         
         sql.Append (" UPDATE MMA_PARAMETROS SET PARAMETRO_VALOR ='")
         sql.Append (pFechaHoraEjec)
         sql.Append ("' WHERE ID = 80")
               
         If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
               Return True
         Else
               Return false       
         End If   

     End Function

     Public Shared Function ObtenerTiempoEJECUDIEnMin() As Integer 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiempo As New DataTable()

        Try

            sSql = "select (DATEDIFF (mi,CONVERT (datetime, (SELECT PARAMETRO_VALOR  FROM MMA_PARAMETROS WHERE ID = 80)),CURRENT_TIMESTAMP)) - (SELECT CONVERT(int, PARAMETRO_VALOR)  FROM MMA_PARAMETROS WHERE ID = 79) as TIEMPOEJEC"

            dtTiempo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTiempo.Rows(0)("TIEMPOEJEC")

        Catch ex As Exception
            Throw ex
        End Try

    End Function


    'FIN Candado ejecucion UDI 2015-04-10 IRI

 
#End Region


  
  #Region "VALMER"


    Public Shared Function ObtenerTipoEmisoraParaTipoValor(ByVal tipoValor As String) As Integer

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT tipo_emisora from MMA_CAT_TIPO_VALOR_VALMER where tipo_valor = '" & tipoValor & "';"

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            If dtFondo.Rows.Count = 1 Then
                Return dtFondo(0)("tipo_emisora")
            Else
                Return 0
            End If


        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtenerTiposDeEmisora() As DataTable 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiposEmisoras As New DataTable()

        Try

            sSql = "SELECT distinct tipo_emisora from MMA_CAT_TIPO_VALOR_VALMER;"

            dtTiposEmisoras = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTiposEmisoras

        Catch ex As Exception
            Throw ex
        End Try

    End Function
    


    Public Shared Function ObtenerTiposValmer(ByVal emisora As CTipoValorValmer.TipoEmisora ) As DataTable 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiposValor As New DataTable()

        Try

            sSql = "SELECT  tipo_valor from MMA_CAT_TIPO_VALOR_VALMER WHERE tipo_emisora = " & emisora 

            dtTiposValor = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTiposValor

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function AltaTipoValorValmer(ByVal tipoEmisora As CTipoValorValmer.TipoEmisora, ByVal tipoValor As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try
                                          

            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_TIPO_VALOR_VALMER WHERE TIPO_EMISORA = " & tipoEmisora   & " AND TIPO_VALOR = '" & tipoValor   & "')"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_TIPO_VALOR_VALMER   (TIPO_EMISORA,TIPO_VALOR) VALUES (" & tipoEmisora  & ", '" & tipoValor  & "')"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return True 
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function ActualizarTipoValorValmer(ByVal tipoEmisora As CTipoValorValmer.TipoEmisora, ByVal tipoValorANT As String,ByVal tipoValorNVO As String ) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

           
                sSql = "IF EXISTS(SELECT * FROM MMA_CAT_TIPO_VALOR_VALMER WHERE TIPO_EMISORA = " & tipoEmisora   & " AND TIPO_VALOR = '" & tipoValorANT   & "')"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_TIPO_VALOR_VALMER SET TIPO_VALOR = '" & tipoValorNVO & "' WHERE TIPO_EMISORA  =" & tipoEmisora & " AND TIPO_VALOR  = '" & tipoValorANT & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
           

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return true
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function EliminarTipoValorValmer(ByVal tipoEmisora As CTipoValorValmer.TipoEmisora, ByVal tipoValor As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE FROM MMA_CAT_TIPO_VALOR_VALMER WHERE TIPO_EMISORA  =" & tipoEmisora & " AND TIPO_VALOR  = '" & tipoValor & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function





 #Region "Masiva VALMER"

 

     Public Shared Function ObtenerParamEjecMasivaVALMER() As datatable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiposEmisoras As New DataTable()

        Try

            sSql = "SELECT PARAMETRO_VALOR,FECHA_ALTA FROM MMA_PARAMETROS WHERE ID = 47;"

            dtTiposEmisoras = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTiposEmisoras

        Catch ex As Exception
            Throw ex
        End Try

    End Function

     Public Shared Function ActualizarParametrosMasivosValmer(ByRef param As CValmerServicio.paramMasiva  ,ByRef sMsgError As String ) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        
        

        Dim regAfec   As Integer 

        sMsgError = ""

        Try
                                                   
                sSql = " UPDATE MMA_PARAMETROS  SET PARAMETRO_VALOR = '" & param.ejecutar  & "', FECHA_ALTA = '" & param.sFechaeject   & "' WHERE ID = 47; "
          
                return    db.EjecutarComando (sSql,pm,regAfec,sMsgError) 
           
        Catch ex As Exception
            Return false
        End Try
    End Function

     Public Shared Function AltaInstrumentMasivosValmer(ByRef lstDatos As ObservableCollection(Of  CTInstrumentosValmerBatch),ByRef sMsgError As String ) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sSqlBatch As String = ""
        

        Dim regAfec   As Integer 

        sMsgError = ""

        Try
                                          
            For Each instrum As CTInstrumentosValmerBatch In lstDatos 

                sSql = " INSERT INTO MMA_OP_MASIVA_VALMER (GRUPO,SECTYPE,NUM_INSTRUMENTO,DESC_INSTRUMENTO) VALUES ('" & instrum.Grupo & "'," & _
                                                                                                                        instrum.Tipo  & ","  & _
                                                                                                                        instrum.NumInstrumento  & ",'" & _
                                                                                                                        instrum.DescInstrumento & "');"
                sSqlBatch &= sSql & vbCr 
            Next


           return    db.EjecutarComando (sSqlBatch,pm,regAfec,sMsgError) 
           

        Catch ex As Exception
            Return false
        End Try
    End Function

     Public Shared Function ExistenNumerosDeInstrumentos() As Boolean 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiposEmisoras As New DataTable()

        Try

            sSql = "SELECT COUNT(*) as NUMREG FROM MMA_OP_MASIVA_VALMER;"

            dtTiposEmisoras = db.EjecutarConsulta(sSql, pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If


            If dtTiposEmisoras.Rows(0)("NUMREG") > 0 then 
                Return true
            else
                Return False 
            End If
            

        Catch ex As Exception
            Throw ex
        End Try

     End Function
    
     Public Shared Function ObtenerNumInstrumentosEjecMasivaVALMER() As datatable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiposEmisoras As New DataTable()

        Try

            sSql = "SELECT GRUPO,SECTYPE,NUM_INSTRUMENTO,DESC_INSTRUMENTO FROM MMA_OP_MASIVA_VALMER;"

            dtTiposEmisoras = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTiposEmisoras

        Catch ex As Exception
            Throw ex
        End Try

    End Function
    
     Public Shared Function BorrarNumInstrumentoMasivosValmer(ByRef sMsgError As String ) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String              
        Dim regAfec   As Integer 

        sMsgError = ""

        Try
                                                   
                sSql = " DELETE FROM MMA_OP_MASIVA_VALMER; "          
                return    db.EjecutarComando (sSql,pm,regAfec,sMsgError) 
           
        Catch ex As Exception
            Return false
        End Try
    End Function


      Public Shared Function ObtenerRegistrosBitBatchValmer(ByVal fechaIni As String, ByVal fechaFin As String, ByVal hayCondicion As Boolean) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            If hayCondicion Then
                sSql = "SELECT FECHA_INI, FECHA_FIN,USUARIO, MODULO,FUNCION,DESCRIPCION  from MMA_OP_BITACORA WHERE FECHA_INI BETWEEN " _
                     & " '" & Convert.ToDateTime(fechaIni).ToString("yyyy-MM-dd HH:mm:ss") & "' AND '" & Convert.ToDateTime(fechaFin).ToString("yyyy-MM-dd HH:mm:ss") & "'  AND modulo ='MasivaValmer'  ORDER BY FECHA_INI"
            Else
                sSql = "SELECT  FECHA_INI, FECHA_FIN,USUARIO, MODULO,FUNCION,DESCRIPCION  from MMA_OP_BITACORA where modulo ='MasivaValmer' ORDER BY FECHA_INI "
            End If


            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo

        Catch ex As Exception
            Throw ex
        End Try


    End Function


#End Region


#End Region


  #Region "BENCH"



    Public Shared Function ObtenerCveBenchDecalog() As DataTable 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT NOM_BENCH_RGOS,CVE_BENCH_DECALOG FROM MMA_CAT_BENCH"

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo 

        Catch ex As Exception
            Return Nothing 
        End Try

    End Function

    Public Shared Function AltaNombreBench(ByVal nombreRgo As String, ByVal nombreDec As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try
            
             Dim id As Integer
        

            id = CServicioLocalSQLServerDB.ObtenerIdSigTabla("MMA_CAT_BENCH")

            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_BENCH WHERE NOM_BENCH_RGOS = '" & nombreRgo  & "' AND CVE_BENCH_DECALOG = '" & nombreDec  & "')"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_BENCH  (ID,NOM_BENCH_RGOS,CVE_BENCH_DECALOG) VALUES (" & id & ", '" & nombreRgo & "','" & nombreDec & "')"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return True 
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function ActualizarNombreBench(ByVal nombreRgoANT As String, ByVal nombreDecANT As String,ByVal nombreRgoNVO As String, ByVal nombreDecNVO As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

           
                sSql = "IF EXISTS(SELECT * FROM MMA_CAT_BENCH WHERE NOM_BENCH_RGOS = '" & nombreRgoANT  & "' AND CVE_BENCH_DECALOG = '" & nombreDecANT  & "')"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_BENCH SET NOM_BENCH_RGOS = '" & nombreRgoNVO & "', CVE_BENCH_DECALOG = '" & nombreDecNVO & "' WHERE  NOM_BENCH_RGOS ='" & nombreRgoANT & "' AND CVE_BENCH_DECALOG  = '" & nombreDecANT & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
           

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return true
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function EliminarNombreBench(ByVal nombreRgo As String, ByVal nombreDec As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE FROM MMA_CAT_BENCH WHERE NOM_BENCH_RGOS = '" & nombreRgo  & "' AND CVE_BENCH_DECALOG = '" & nombreDec  & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function ObtenerRutaArchivoBench() As String
        Return ObtenerParametro(29)
    End Function

    Public Shared Function ObtenerNombreArchivoBench() As String
        Return ObtenerParametro(30)
    End Function

    Public Shared Function ObtenerNombreComandoTLoderBench() As String
        Return ObtenerParametro(31)
    End Function

    Public Shared Function ObtenerNombreArchivoRespBench() As String
        Return ObtenerParametro(32)
    End Function

    Public Shared Function ObtenerParamComandoTLoderBench() As String
        Return ObtenerParametro(33)
    End Function

#End Region


#Region "LoadIntrument"


    Public Shared Function ObtenerPosicionesNumEnArchValmer() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPosiciones As New DataTable()

        Try

            sSql = "SELECT ID_CAMPO,NOMBRE_CAMPO,POSICION_BANCARIOS, POSICION_GUBERNAMENTAL,POSICION_CORPORATIVOS,POSICION_NOTAS, POSICION_VECTOR_VALMER FROM MMA_CAT_CAMPOS_VALMER"

            dtPosiciones = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtPosiciones

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtenerTipoLI(ByVal tipoValor As String) As String

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT top(1) TIPO_EMISORA,DESC_EMISORA FROM MMA_CAT_TIPO_VALOR_LI WHERE tv = '" & tipoValor & "' ORDER BY tipo_emisora ASC"

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            If dtFondo.Rows.Count = 1 Then
                Return dtFondo(0)("DESC_EMISORA")
            Else
                Return "NO ENCONTRADO"
            End If


        Catch ex As Exception
            Return "NO ENCONTRADO"
        End Try

    End Function

    Public Shared Function ObtenerCamposVectorValmer() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT a.id_campo, a.nom_campo,a.campo_decalog, a.vector_valmer,b.POSICION_VECTOR_VALMER FROM MMA_CAT_CAMPO_LI a inner join MMA_CAT_CAMPOS_VALMER b on (a.id_campo = b.id_campo) WHERE vector_valmer = 1"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtienerCamposDECALOGLI() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT id_campo, nom_campo, campo_decalog FROM MMA_CAT_CAMPO_LI WHERE CAMPO_DECALOG IS NOT NULL ORDER BY ID_CAMPO ASC;"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtienerNombreCamposLI() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT id_campo, nom_campo, campo_decalog FROM MMA_CAT_CAMPO_LI ORDER BY ID_CAMPO ASC;"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtienerTipoInstDecalog(ByVal tv As String) As String
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtDatos As New DataTable()

        Try

            sSql = " SELECT VALOR_CORRESPON  FROM MMA_CAT_DECALOGDATA_LI WHERE VALOR_BUSCADO = '" & tv & "'"

            dtDatos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return ""
            End If

            If dtDatos.Rows.Count > 0 Then
                Return dtDatos.Rows(0)(0)

            Else
                Return ""
            End If

        Catch ex As Exception
            Return ""
        End Try
    End Function
    
    Public Shared Function ObtenerTiposLI(ByVal emisora As CTipoValorLI.TipoEmisora  ) As DataTable 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtTiposValor As New DataTable()

        Try

            sSql = "SELECT  tv from MMA_CAT_TIPO_VALOR_LI WHERE tipo_emisora = " & emisora 

            dtTiposValor = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtTiposValor

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function AltaTipoValorLI(ByVal tipoEmisora As CTipoValorLI.TipoEmisora,ByVal descTipoEmisora As String  , ByVal tipoValor As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try
                                          
            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_TIPO_VALOR_LI WHERE TIPO_EMISORA = " & tipoEmisora   & " AND TV = '" & tipoValor   & "')"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_TIPO_VALOR_LI   (TIPO_EMISORA,desc_emisora,TV) VALUES (" & tipoEmisora  & ",'" & descTipoEmisora &"', '" & tipoValor  & "')"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return True 
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function ActualizarTipoValorLI(ByVal tipoEmisora As CTipoValorLI.TipoEmisora  , ByVal tipoValorANT As String,ByVal tipoValorNVO As String ) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

           
                sSql = "IF EXISTS(SELECT * FROM MMA_CAT_TIPO_VALOR_LI WHERE TIPO_EMISORA = " & tipoEmisora   & " AND TV = '" & tipoValorANT   & "')"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_TIPO_VALOR_LI SET TV = '" & tipoValorNVO & "' WHERE TIPO_EMISORA  =" & tipoEmisora & " AND TV  = '" & tipoValorANT & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
           

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return true
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function EliminarTipoValorLI(ByVal tipoEmisora As CTipoValorLI.TipoEmisora, ByVal tipoValor As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE FROM MMA_CAT_TIPO_VALOR_LI WHERE TIPO_EMISORA  =" & tipoEmisora & " AND TV  = '" & tipoValor & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function
    
    Public Shared Function ObtenerDECDATALI() As DataTable 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT VALOR_BUSCADO,VALOR_CORRESPON FROM MMA_CAT_DECALOGDATA_LI"

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondo 

        Catch ex As Exception
            Return Nothing 
        End Try

    End Function

    Public Shared Function AltaDECDATALI(ByVal valBuscado As String, ByVal valorCorrespond As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try
            
             Dim idBusqueda As Integer
        

            idBusqueda = 1  'Mientras el usuario define sí existe dos o más tipos de búsqueda

            sSql = "IF NOT EXISTS(SELECT * FROM MMA_CAT_DECALOGDATA_LI WHERE VALOR_BUSCADO = '" & valBuscado  & "' AND VALOR_CORRESPON = '" & valorCorrespond  & "')"
            sSql = sSql & " BEGIN INSERT INTO MMA_CAT_DECALOGDATA_LI  (TIPO_BUSQUEDA,VALOR_BUSCADO,VALOR_CORRESPON) VALUES (" & idBusqueda & ", '" & valBuscado & "','" & valorCorrespond & "')"
            sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return True 
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function ActualizarDECDATALI(ByVal valBusANT As String, ByVal valCorrANT As String,ByVal valBusNVO As String, ByVal valCorrNVO As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

           
                sSql = "IF EXISTS(SELECT * FROM MMA_CAT_DECALOGDATA_LI WHERE VALOR_BUSCADO = '" & valBusANT  & "' AND VALOR_CORRESPON = '" & valCorrANT  & "')"
                sSql = sSql & " BEGIN UPDATE MMA_CAT_DECALOGDATA_LI SET VALOR_BUSCADO  = '" & valBusNVO & "', VALOR_CORRESPON = '" & valCorrNVO & "' WHERE  VALOR_BUSCADO ='" & valBusANT & "' AND VALOR_CORRESPON  = '" & valCorrANT & "'"
                sSql = sSql & " SELECT 0 Error END ELSE BEGIN SELECT 1 Error END"
           

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return false
            End If

            If dtBroker.Rows(0)("Error") = 0 Then
                Return true
            Else
                Return false
            End If

        Catch ex As Exception
            Return false
        End Try
    End Function

    Public Shared Function EliminarDECDATALI(ByVal valBuscado As String, ByVal valorCorrespond As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBroker As New DataTable()

        Try

            sSql = " DELETE FROM MMA_CAT_DECALOGDATA_LI WHERE VALOR_BUSCADO = '" & valBuscado  & "' AND VALOR_CORRESPON = '" & valorCorrespond  & "'"

            dtBroker = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function


    Public Shared Function ObtenerRutaArchivoLoadInstrument() As String
        Return ObtenerParametro(22)
    End Function


    Public Shared Function ObtenerNombreArchivoLoadInstrument() As String
        Return ObtenerParametro(38)
    End Function


    Public Shared Function ObtenerNombreComandoTLoderLoadInstrument() As String
        Return ObtenerParametro(36)
    End Function

    Public Shared Function ObtenerParamComandoTLoderLoadInstrument() As String
        Return ObtenerParametro(37)
    End Function

    

#End Region


#Region "Campos Valmer"
    Public Shared Function ObtieneCamposValmer() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT ID_CAMPO,NOMBRE_CAMPO,POSICION_BANCARIOS,POSICION_GUBERNAMENTAL,POSICION_CORPORATIVOS,POSICION_NOTAS,POSICION_VECTOR_VALMER FROM MMA_CAT_CAMPOS_VALMER ORDER BY ID_CAMPO"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function GuardaCamposValmer(ByVal Cadena As String, ByVal IdUsuario As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try
            sSql = "SET ARITHABORT ON " & vbNewLine
            sSql = sSql & "DECLARE @Cadena XML = '" & Cadena & "'" & vbNewLine & vbNewLine
            sSql = sSql & " UPDATE A SET A.NOMBRE_CAMPO = B.Nombre, A.POSICION_BANCARIOS = CASE WHEN (LEN(B.PosicionBancarios)=0) THEN NULL ELSE B.PosicionBancarios END," & vbNewLine
            sSql = sSql & " A.POSICION_GUBERNAMENTAL = CASE WHEN (LEN(B.PosicionGubernamental)=0) THEN NULL ELSE B.PosicionGubernamental END," & vbNewLine
            sSql = sSql & " A.POSICION_CORPORATIVOS = CASE WHEN (LEN(B.PosicionCorporativos)=0) THEN NULL ELSE B.PosicionCorporativos END," & vbNewLine
            sSql = sSql & " A.POSICION_NOTAS = CASE WHEN (LEN(B.PosicionNotas)=0) THEN NULL ELSE B.PosicionNotas END," & vbNewLine
            sSql = sSql & " A.POSICION_VECTOR_VALMER = CASE WHEN (LEN(B.PosicionVector)=0) THEN NULL ELSE B.PosicionVector END," & vbNewLine
            sSql = sSql & " A.FECHA_MODIFICACION = GETDATE(), A.USUARIO_MODIFICACION = " & IdUsuario & vbNewLine
            sSql = sSql & " FROM MMA_CAT_CAMPOS_VALMER A INNER JOIN (SELECT Tbl.Col.value('Id[1]', 'int') Id,Tbl.Col.value('Nombre[1]', 'varchar(45)') Nombre, Tbl.Col.value('PosicionBancarios[1]','varchar(10)') PosicionBancarios," & vbNewLine
            sSql = sSql & " Tbl.Col.value('PosicionGubernamental[1]','varchar(10)') PosicionGubernamental, Tbl.Col.value('PosicionCorporativos[1]','varchar(10)') PosicionCorporativos," & vbNewLine
            sSql = sSql & " Tbl.Col.value('PosicionNotas[1]','varchar(10)') PosicionNotas,Tbl.Col.value('PosicionVector[1]','varchar(10)') PosicionVector" & vbNewLine
            sSql = sSql & " FROM @Cadena.nodes('//Campo') Tbl(Col)) B ON A.ID_CAMPO = B.Id"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function
#End Region


#Region "API Asset Allocation"
    Public Shared Function ObtieneRutaTLOADERAPI() As String
        Return ObtenerParametro(49)
    End Function

    Public Shared Function ObtieneRutaArchivoAPI() As String
        Return ObtenerParametro(50)
    End Function

    Public Shared Function ObtieneParametrosTLOADERAPI() As String
        Return ObtenerParametro(51)
    End Function

    Public Shared Function ObtieneOrderTypeAlternativo() As String
        Return ObtenerParametro(52)
    End Function

    Public Shared Function ObtieneFormatoAPI(ByVal intTipo As Integer, ByVal intSubtipo As Integer) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SET NOCOUNT ON SELECT NOMBRE_CAMPO,CASE WHEN NOMBRE_CAMPO = 'ORDR_VALID_DAT' THEN CONVERT(VARCHAR,GETDATE(),105) ELSE VALOR_CAMPO END VALOR_CAMPO,ISNULL(ORDEN,0) ORDEN, VALIDA_ENVIO FROM MMA_CAT_FORMATO_API WHERE TIPO = " & intTipo.ToString() & " AND SUBTIPO = " & intSubtipo.ToString() & " ORDER BY ID"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ActualizaDetalleErrorDECALOG(ByVal intId As Integer, ByVal strMensaje As String, ByVal intUsuario As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try
            sSql = "UPDATE MMA_OP_BITACORA_DECALOG SET FECHA_MODIFICACION = GETDATE(), USUARIO_MODIFICACION = " & intUsuario & ",DETALLE_ERROR = '" & strMensaje & "' WHERE ID = " & intId

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ObtieneParametrosAPI(ByVal Opcion As Integer) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SET NOCOUNT ON SELECT PARAMETRO_VALOR,PARAMETRO_EQUIVALENCIA FROM MMA_CAT_PARAMETRO_API WHERE PARAMETRO_OPCION_CAMPO = " & Opcion.ToString()

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function
   
#End Region


#Region "Contingencia APIC"

    Public Shared Function ObtieneMaxRenglones() As String
        Return ObtenerParametro(86)
    End Function

    Public Shared Function ObtieneRutaTLOADERAPIC() As String
        Return ObtenerParametro(87)
    End Function

    Public Shared Function ObtieneRutaArchivoAPICPosicion() As String
        Return ObtenerParametro(88)
    End Function

    Public Shared Function ObtieneRutaArchivoAPICEfectivo() As String
        Return ObtenerParametro(89)
    End Function

    Public Shared Function ObtieneParametrosTLOADERAPIC() As String
        Return ObtenerParametro(90)
    End Function

    Public Shared Function ObtieneNombreReporteLiquidez() As String
        Return ObtenerParametro(93)
    End Function

#End Region


#Region "OBTENCION DE PARAMETROS"



    Public Shared Function ObtieneRutaDECALOG() As String
        Return ObtenerParametro(3)
    End Function

    Public Shared Function ObtenerRutaArchivoLiquidez() As String
        Return ObtenerParametro(23)
    End Function

    Public Shared Function ObtenerNombreArchivoLiquidez() As String
        Return ObtenerParametro(24)
    End Function

    Public Shared Function ObtenerNombreComandoTLoader() As String
        Return ObtenerParametro(25)
    End Function

    Public Shared Function ObtenerParamComandoTLoaderLiq() As String
        Return ObtenerParametro(26)
    End Function

    Public Shared Function ObtenerParamComandoTLoaderLiqBanc() As String
        Return ObtenerParametro(69)
    End Function

    Public Shared Function ObtenerParamUsuarioTLoader() As String
        Return ObtenerParametro(75)
    End Function

    Public Shared Function ObtenerParamComandoTLoaderPorv() As String
        Return ObtenerParametro(28)
    End Function



    Public Shared Function ObtenerNombreArchivoPorvenir() As String
        Return ObtenerParametro(27)
    End Function

    Public Shared Function ObtenerRutaArchivoValmer() As String
        Return ObtenerParametro(34)
    End Function

    Public Shared Function ObtenerNombreArchivoValmer() As String
        Return ObtenerParametro(35)
    End Function

    Public Shared Function ObtenerNombreComandoTLoderValmer() As String
        Return ObtenerParametro(39)
    End Function

    Public Shared Function ObtenerParamComandoTLoderValmer() As String
        Return ObtenerParametro(40)
    End Function

    Public Shared Function ObtenerParamComandoTLoderValmerBatch() As String
        Return ObtenerParametro(48)
    End Function

    Public Shared Function ObtenerRutaHTMLCorporativo() As String
        Return ObtenerParametro(53)
    End Function

    Public Shared Function ObtenerRutaHTMLGubernamental() As String
        Return ObtenerParametro(54)
    End Function

    Public Shared Function ObtenerRutaHTMLBancaria() As String
        Return ObtenerParametro(55)
    End Function

    Public Shared Function ObtenerRutaHTMLNotas() As String
        Return ObtenerParametro(56)
    End Function

    Public Shared Function ObtenerRutaHTMLEurobonos() As String
        Return ObtenerParametro(73)
    End Function

    Public Shared Function ObtieneRutaValmer() As String
        Return ObtenerParametro(2)
    End Function

    Public Shared Function ObtieneRutaTransaccionesABorrar() As String
        Return ObtenerParametro(4)
    End Function

    Public Shared Function ObtieneRutaChequerasABorrar() As String
        Return ObtenerParametro(5)
    End Function

    Public Shared Function ObtieneRutaTLOADER() As String
        Return ObtenerParametro(6)
    End Function

    Public Shared Function ObtieneInicioRengCOFAVTLPorvenir() As String
        Return ObtenerParametro(91)
    End Function

    Public Shared Function ObtieneRutaTLOADERTraspasos() As String
        Return ObtenerParametro(8)
    End Function

    Public Shared Function ObtieneRutaArchivoTraspasos() As String
        Return ObtenerParametro(9)
    End Function

    Public Shared Function ObtieneRutaFactorUDI() As String
        Return ObtenerParametro(10)
    End Function

    Public Shared Function ObtieneRutaArchivoFactorUDI() As String
        Return ObtenerParametro(11)
    End Function

    Public Shared Function ObtieneRutaFTP() As String
        Return ObtenerParametro(12)
    End Function

    Public Shared Function ObtieneRutaBatIssue() As String
        Return ObtenerParametro(18)
    End Function

    Public Shared Function ObtieneNombreJerarquia() As String
        Return ObtenerParametro(19)
    End Function

    Public Shared Function ObtieneRutaArchivoResumenReporting() As String
        Return ObtenerParametro(44)
    End Function

    Public Shared Function ObtieneRutaArchivoExpRebalancing() As String
        Return ObtenerParametro(45)
    End Function

    Public Shared Function ObtieneRutaArchivoExpTLoader() As String
        Return ObtenerParametro(46)
    End Function

    Public Shared Function ObtieneRutaArchivoReporting() As String
        Return ObtenerParametro(41)
    End Function

    Public Shared Function ObtieneRutaTLOADERReporting() As String
        Return ObtenerParametro(42)
    End Function

    Public Shared Function ObtieneParametrosTLOADERReporting() As String
        Return ObtenerParametro(43)
    End Function

    Public Shared Function ObtieneParametrosTLOADERCOVAFTLTraspasos() As String
        Return ObtenerParametro(20)
    End Function

    Public Shared Function ObtenerNumMaxArchivosAdjuntos() As String
        Return ObtenerParametro(60)
    End Function

    Public Shared Function ObtenerTamanoMaxTotalArchivosAdjuntos() As String
        Return ObtenerParametro(61)
    End Function

    Public Shared Function ObtenerDireccionMailRemitente() As String
        Return ObtenerParametro(62)
    End Function

    Public Shared Function ObtenerSubjectGenerico() As String
        Return ObtenerParametro(63)
    End Function

    Public Shared Function ObtenerBodyGenericoSPA() As String
        Return ObtenerParametro(64)
    End Function

    Public Shared Function ObtenerBodyGenericoENG() As String
        Return ObtenerParametro(74)
    End Function

    Public Shared Function ObtenerSERVIDORSMTP() As String
        Return ObtenerParametro(65)
    End Function

    Public Shared Function ObtenerPUERTOSMTP() As String
        Return ObtenerParametro(66)
    End Function

    Public Shared Function ObtenerTolRemMinXFondo() As String
        Return ObtenerParametro(70)
    End Function

    Public Shared Function ObtenerNombreArchivoEventos() As String
        Return ObtenerParametro(71)
    End Function

    Public Shared Function ObtenerEncabezadoEventos() As String
        Return ObtenerParametro(72)
    End Function

    Public Shared Function ObtenerSwitchFuenteXMLParaFLRepo() As String
        Return ObtenerParametro(77)
    End Function

    Public Shared Function ObtenerRutaDefXMLParaReposEnFL() As String
        Return ObtenerParametro(78)
    End Function

    Public Shared Function ObtenerNombreArchivoTLTranListed() As String
        Return ObtenerParametro(81)
    End Function

    Public Shared Function ObtenerRutaArchivoTLTranListed() As String
        Return ObtenerParametro(82)
    End Function

    Public Shared Function ObtenerNombreArchivoTLoaderTranListed() As String
        Return ObtenerParametro(83)
    End Function

    Public Shared Function ObtenerParametrosTLoaderTranListed() As String
        Return ObtenerParametro(84)
    End Function


    Public Shared Function ObtenerIdGrupoTranListed() As Integer

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = " BEGIN TRAN "
            sSql &= " UPDATE MMA_PARAMETROS SET PARAMETRO_VALOR = (CONVERT(INT, PARAMETRO_VALOR) + 1) WHERE ID = 85 "
            sSql &= " SELECT  PARAMETRO_VALOR  FROM MMA_PARAMETROS WHERE ID = 85  "
            sSql &= " COMMIT TRAN "

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return ""
            End If

            If dtFondo.Rows.Count = 1 Then
                Return Integer.Parse(dtFondo(0)("PARAMETRO_VALOR"))
            Else
                Return -1
            End If

        Catch ex As Exception
            Return -1
        End Try


    End Function



    Public Shared Function ObtenerParametro(ByVal idParametro As Integer) As String

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try

            sSql = "SELECT PARAMETRO_VALOR FROM MMA_PARAMETROS WHERE ID = " & idParametro

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return ""
            End If

            If dtFondo.Rows.Count = 1 Then
                Return dtFondo(0)("PARAMETRO_VALOR")
            Else
                Return ""
            End If

        Catch ex As Exception
            Return ""
        End Try

    End Function

#End Region

#Region "EXTRACCION FICHEROS"


    Public Shared Function ObtieneConsultas(ByVal idConsulta As Integer) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try
            If idConsulta = Nothing Then
                sSql = "SELECT * FROM MMA_CAT_CONSULTA ORDER BY NOMBRE_CONSULTA"
            Else
                sSql = "SELECT * FROM MMA_CAT_CONSULTA WHERE ID = " & idConsulta
            End If


            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function ActualizaConsecutivo(ByVal idConsulta As Integer) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()

        Try
            sSql = "UPDATE MMA_CAT_CONSULTA SET CONSECUTIVO = CASE WHEN CONVERT(VARCHAR(10), GETDATE(), 101) = CONVERT(VARCHAR(10), ULTIMA_FECHA, 101) THEN CONSECUTIVO + 1  ELSE 1 END, ULTIMA_FECHA = GETDATE() WHERE ID = " & idConsulta

            dtFondo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            Else
                Return True
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function

#End Region



#Region "BITACORA DE SISTEMA"


    Public Shared Function ObtenerIdRegEventoDB() As ULong

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sql = New System.Text.StringBuilder()
        Dim sMsgError As String = ""

        Dim dtIdRegEvt As New DataTable()


        sql.Append(" declare @nuevoValor int; ")
        sql.Append(" exec @nuevoValor = sp_FRIDA_ObtenerIdEvento; ")
        sql.Append(" select @nuevoValor as IdRegEvt; ")

        dtIdRegEvt = db.EjecutarConsulta(sql.ToString(), pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return 0
        End If

        Return dtIdRegEvt.Rows(0).Item("IdRegEvt")

    End Function

    Public Shared Function ObtenerIdRegErrorDB() As ULong

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sql = New System.Text.StringBuilder()
        Dim sMsgError As String = ""

        Dim dtIdRegErr As New DataTable()


        sql.Append(" declare @nuevoValor int; ")
        sql.Append(" exec @nuevoValor = sp_FRIDA_ObtenerIdError; ")
        sql.Append(" select @nuevoValor as IdRegErr; ")

        dtIdRegErr = db.EjecutarConsulta(sql.ToString(), pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return 0
        End If

        Return dtIdRegErr.Rows(0).Item("IdRegErr")



    End Function



    Public Shared Function GuardarEventoBit(ByRef eventoBit As CSBitEvento) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")



        sql.Append(" INSERT INTO BIT_OP_EVENTO (ID_REG,ID_EVENTO,FEC_INI,ID_USU_FRIDA,DESCRIPCION) VALUES (")
        sql.Append(eventoBit.IdRegistro)
        sql.Append(",")
        sql.Append(eventoBit.CveEvento)
        sql.Append(", CURRENT_TIMESTAMP,")
        sql.Append(eventoBit.IdUsuFrida)
        sql.Append(",'")
        sql.Append(eventoBit.Descripcion)
        sql.Append("');")


        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If

    End Function


    Public Shared Function GuardarDetalleEventoBit(ByVal idRegEvento As Long, ByVal campo As String, ByVal valor As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")


        sql.Append(" INSERT INTO BIT_OP_EVENTO_DET (ID_REG,FEC_REG,CAMPO,VALOR) VALUES (")
        sql.Append(idRegEvento)
        sql.Append(",CURRENT_TIMESTAMP,'")
        sql.Append(campo)
        sql.Append("','")
        sql.Append(valor)
        sql.Append("')")

        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function GuardarDetallesEventoBit(ByVal IdRegEvento As Long, ByRef lstDetalles As List(Of CTBitEventoDet)) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")


        For Each detalle As CTBitEventoDet In lstDetalles

            sql.Append(" INSERT INTO BIT_OP_EVENTO_DET (ID_REG,FEC_REG,CAMPO,VALOR) VALUES (")
            sql.Append(IdRegEvento)
            sql.Append(",CURRENT_TIMESTAMP,'")
            sql.Append(detalle.Campo)
            sql.Append("','")
            sql.Append(detalle.Valor)
            sql.Append("');")

            sql.AppendLine()
        Next


        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ActualizarEventoBit(ByVal IdRegEvento As Long) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")


        sql.Append(" UPDATE BIT_OP_EVENTO SET FEC_FIN = CURRENT_TIMESTAMP WHERE ID_REG =")
        sql.Append(IdRegEvento)

        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If


    End Function



    Public Shared Function GuardarErrorBit(ByRef errorBit As CSBitError) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")


        sql.Append(" INSERT INTO BIT_OP_ERROR (ID_ERROR,ID_REG,FEC_REGISTRO,MENSAJE) VALUES (")
        sql.Append(errorBit.IdError)
        sql.Append(",")
        sql.Append(errorBit.IdRegEvento)
        sql.Append(",CURRENT_TIMESTAMP,'")
        sql.Append(errorBit.Mensaje.Replace("'", "%"))
        sql.Append("');")


        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function GuardarDetalleErrorBit(ByVal idRegError As Long, ByVal campo As String, ByVal valor As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")


        sql.Append(" INSERT INTO BIT_OP_ERROR_DET (ID_ERROR,CAMPO,VALOR) VALUES (")
        sql.Append(idRegError)
        sql.Append(",'")
        sql.Append(campo)
        sql.Append("','")
        sql.Append(valor)
        sql.Append("');")

        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function GuardarDetallesErrorBit(ByVal IdRegError As Long, ByRef lstDetalles As List(Of CTBitErrorDet)) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0
        Dim sMsgerror As String = ""

        Dim sql = New StringBuilder("")


        For Each detalle As CTBitErrorDet In lstDetalles

            sql.Append(" INSERT INTO BIT_OP_ERROR_DET (ID_ERROR,CAMPO,VALOR) VALUES (")
            sql.Append(IdRegError)
            sql.Append(",CURRENT_TIMESTAMP,'")
            sql.Append(detalle.Campo)
            sql.Append("','")
            sql.Append(detalle.Valor)
            sql.Append("');")

            sql.AppendLine()
        Next


        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgerror) = True Then
            Return True
        Else
            Return False
        End If

    End Function


#End Region



#Region "ELIMINACION TRIPLICADO DE OP (TRANSACCIONES VIA MARKET LISTED)"

    Public Shared Function ObtienerInfoValidacionETO() As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtValores As New DataTable()

        Try

            sSql = "SELECT A.ID_CAT,A.CLAVE, B.VALOR, A.VAL_DEFAULT  FROM GEN_VALIDACION A INNER JOIN GEN_CAT_GEN B ON (A.ID_CAT = B.ID_CAT AND A.CLAVE = B.CLAVE) WHERE  A.ID_FUN = " & CSOrdMarketListed.FUNCIONALIDAD_ETO_LISTED & " ORDER BY  A.ID_CAT,A.CLAVE"

            dtValores = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtValores

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Shared Function AlmacerLogTransaccionesListedDB(ByRef lstTransacciones As List(Of CTOrdenMarketListed), ByVal nomArchivo As String, ByRef sMsgError As String) As Boolean


        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0


        Dim sql = New StringBuilder("")


        Dim id_grpo_tran As Integer = ObtenerIdGrupoTranListed()

        If id_grpo_tran < 0 Then
            sMsgError = "No se pudo generar el ID del Grupo de transacciones."
            Return False
        End If


        For Each tran As CTOrdenMarketListed In lstTransacciones





            sql.Append(" INSERT INTO MKT_TRANSAC_LISTED (PORTAFOLIO,INSTRUMENTO,PRECIO,CANTIDAD_TIT,TIPO_TRAN,ESTRATEGIA,FEC_TRAN,FEC_LIQ,BROKER,FEC_REGISTRO,FOLIO_DEC,DESCRIPCION, NOM_ARCHVO, ID_TRAN, ID_GPO_TRAN,USUARIO) VALUES (")
            sql.Append("'" & tran.Portafolio & "',")
            
            sql.Append ("'" & tran.Instrumento.Replace("_","") & "',")  ' Se eliminan guiones bajos.

            sql.Append(tran.Precio & ",")
            sql.Append(tran.CantidadTit & ",")
            sql.Append("'" & tran.TipoTran & "',")
            sql.Append("'" & tran.Strategia & "',")
            sql.Append("'" & tran.FecTran.Substring(6, 4) & "-" & tran.FecTran.Substring(3, 2) & "-" & tran.FecTran.Substring(0, 2) & "',")
            sql.Append("'" & tran.FecLiq.Substring(6, 4) & "-" & tran.FecLiq.Substring(3, 2) & "-" & tran.FecLiq.Substring(0, 2) & "',")
            sql.Append("'" & tran.Broker & "',")
            sql.Append(" CURRENT_TIMESTAMP,")
            sql.Append(" NULL,")
            sql.Append(" NULL,'")
            sql.Append(nomArchivo)
            sql.Append("',")
            sql.Append(tran.IdTran & ",")
            sql.Append(id_grpo_tran & ",")
            sql.Append(CServiciosMacrosSyBaseDB.IDUSUARIO & " );")

            sql.AppendLine()

        Next




        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ObtenerPEDDistribParaActualizarFolioDec(ByVal sFechaIni As String, ByVal sFechaFin As String, ByRef sMsgError As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)


        Dim sql = New StringBuilder()

        Dim dtDistribActualizar As New DataTable()

        Try


            sql.Append(" SELECT ID_GPO_TRAN,ID_TRAN,  PORTAFOLIO,INSTRUMENTO,PRECIO,CANTIDAD_TIT,TIPO_TRAN,ESTRATEGIA,FEC_TRAN,FEC_LIQ,BROKER,FEC_REGISTRO, ISNULL(FOLIO_DEC,'') as FOLIO_DEC,isnull(DESCRIPCION,'') AS DESCRIPCION, ISNULL(NOM_ARCHVO,'') AS NOM_ARCHIVO, B.NOMBRE as NOMUSU ")
            sql.Append(" FROM MKT_TRANSAC_LISTED INNER JOIN MMA_CAT_USUARIO B ON (USUARIO = B.ID) ")
            sql.Append(" WHERE ")
            sql.Append(" FEC_REGISTRO BETWEEN '")
            sql.Append(Convert.ToDateTime(sFechaIni).ToString("yyyy-MM-dd HH:mm:ss"))
            sql.Append("' AND '")
            sql.Append(Convert.ToDateTime(sFechaFin).ToString("yyyy-MM-dd HH:mm:ss"))
            sql.Append("' ORDER BY ID_GPO_TRAN,ID_TRAN, NOM_ARCHIVO ASC")



            dtDistribActualizar = db.EjecutarConsulta(sql.ToString(), pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Return Nothing
            End If

            Return dtDistribActualizar

        Catch ex As Exception
            sMsgError = ex.Message
            Return Nothing
        End Try



    End Function


    Public Shared Function AlmacenarRespuestaDecalogTranListed(ByRef lstRespuestaDecalog As List(Of CTRespuestaDecalog), ByRef sMsgError As String) As Boolean


        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim regAfec As Integer = 0

        Dim sql = New StringBuilder("")



        For Each respuesta As CTRespuestaDecalog In lstRespuestaDecalog

            If respuesta.TipoRespuesta = CTRespuestaDecalog.RespuestaDecalog.OK_TEXT Then

                sql.Append("UPDATE MKT_TRANSAC_LISTED SET FOLIO_DEC = '")
                sql.Append(respuesta.Valor)
                sql.Append("' WHERE  ID_GPO_TRAN =")
                sql.Append(respuesta.IdGpoTran)
                sql.Append(" and ID_TRAN =")
                sql.Append(respuesta.IdTran)
                sql.Append(" AND NOM_ARCHVO = '")
                sql.Append(respuesta.NomArchivo)
                sql.Append("';")

                sql.AppendLine()

            Else

                sql.Append("UPDATE MKT_TRANSAC_LISTED SET DESCRIPCION = '")
                sql.Append(CUtileria.PrepararCadenaParaSQL(respuesta.Valor, __DESCRIPCION_LISTED__))
                sql.Append("' WHERE  ID_GPO_TRAN =")
                sql.Append(respuesta.IdGpoTran)
                sql.Append(" and ID_TRAN =")
                sql.Append(respuesta.IdTran)
                sql.Append(" AND NOM_ARCHVO = '")
                sql.Append(respuesta.NomArchivo)
                sql.Append("';")

                sql.AppendLine()

            End If


        Next


        If db.EjecutarComando(sql.ToString(), pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function





#End Region





End Class

